package form;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import chucnang.ChungTu;
import chucnang.DoanhThu;
import chucnang.ThuTien;
import chucnang.TraTien;

import trangchu.UtilDelegate;

public class DSJ_XuatHang extends UtilDelegate {
	
	private ThuTien thutien;
	private ChungTu chungtu;
	private DoanhThu doanhthu;
	public String ID_GDBanHang;
	public String TongThanhToan;
	public String DaTra;
	public String ConLai;
	public String Ngay;
	public String ChungTu;
	public String GhiChu;
	public String LoaiChungTu;
	
	public String getID_GDBanHang() {
		return ID_GDBanHang;
	}

	public void setID_GDBanHang(String iD_GDBanHang) {
		ID_GDBanHang = iD_GDBanHang;
	}

	public String getLoaiChungTu() {
		return LoaiChungTu;
	}

	public void setLoaiChungTu(String loaiChungTu) {
		LoaiChungTu = loaiChungTu;
	}

	public String getGhiChu() {
		return GhiChu;
	}

	public void setGhiChu(String ghiChu) {
		GhiChu = ghiChu;
	}

	public String getNgay() {
		return Ngay;
	}

	public void setNgay(String ngay) {
		Ngay = ngay;
	}

	public String getChungTu() {
		return ChungTu;
	}

	public void setChungTu(String chungTu) {
		ChungTu = chungTu;
	}

	public String getTongThanhToan() {
		return TongThanhToan;
	}

	public void setTongThanhToan(String tongThanhToan) {
		TongThanhToan = tongThanhToan;
	}

	public String getDaTra() {
		return DaTra;
	}

	public void setDaTra(String daTra) {
		DaTra = daTra;
	}

	public String getConLai() {
		return ConLai;
	}

	public void setConLai(String conLai) {
		ConLai = conLai;
	}

	public void insertInto(DSJ_FormMuaHang person,DSJ_NhanVien nhanvien,DSJ_DieuKhoanThanhToan dieukhoan,DSJ_KhachHang khachhang,DSJ_KhoHang kho,DSJ_DonVi donvi){
		Connection con =UtilDelegate.getConnection();
		try {
			Statement st = con.createStatement();
			String str ="INSERT INTO XuatHang(HangHoa_ID,TenHangHoa,KhoHang,TenDonVi,DonGia,SoLuong,ThanhTien,NhanVienXH,NgayXuatHang,KhachHang_ID,TenKHXH,CachThanhToan,HanThanToan,LoaiChungTu,ChungTu,GhiChu) " + "VALUES (" +
			"'"+ person.getMaSP()+ "',N'"+ person.getTenSP()+ "',N'"+ kho.getTenKho()+ "'," +
			"N'"+ donvi.getTenDV()+ "', '"+ person.getGiaBan()+ "', '"+ person.getSoLuong()+ "'," +
			" '"+ person.getThanhTien()+ "', N'"+nhanvien.getHoTenNV()+ "' , '"+ person.getNgayNhapHang()+ "'," +
<<<<<<< .mine
			"'"+ khachhang.getMaKH()+ "',N'"+ khachhang.getHoTenKH()+ "', N'"+ dieukhoan.getTenDK()+ "', '"+ person.getHanThanhToan()+ "', N'"+ person.getLoaiChungTu()+ "')";	
=======
			"'"+ khachhang.getMaKH()+ "',N'"+ khachhang.getHoTenKH()+ "', N'"+ dieukhoan.getTenDK()+ "', '"+ person.getHanThanhToan()+ "', " +
			"N'"+ person.getLoaiChungTu()+ "', '"+ person.getChungTu()+ "', N'"+ person.getGhiChu()+ "')";	
>>>>>>> .r59
			st.execute(str);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			UtilDelegate.closeConnection(con);
		}
	}
	
	public Vector getLoad(){
		Vector<String> b = new Vector<String>();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select top 1 * from (select top (select count(*) from XuatHang) * from XuatHang  order by XuatHang_ID DESC)a");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				
				b.add(rs.getString("HangHoa_ID"));
				b.add(rs.getString("TenHangHoa"));
				b.add(rs.getString("KhoHang"));
				b.add(rs.getString("TenDonVi"));
				b.add(rs.getString("DonGia"));
				b.add(rs.getString("SoLuong"));
				b.add(rs.getString("ThanhTien"));
				b.add(rs.getString("XuatHang_ID"));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return b;
	}

	public Vector getEdit(String ID){
		Integer id = Integer.parseInt(ID);
		Vector<String> b = new Vector<String>();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select * from XuatHang where XuatHang_ID="+id);
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				b.add(rs.getString("HangHoa_ID"));
				b.add(rs.getString("TenHangHoa"));
				b.add(rs.getString("KhoHang"));
				b.add(rs.getString("TenDonVi"));
				b.add(rs.getString("DonGia"));
				b.add(rs.getString("SoLuong"));
				b.add(rs.getString("ThanhTien"));
				b.add(rs.getString("XuatHang_ID"));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return b;
	}
	public void edit(DSJ_FormMuaHang person,DSJ_DonVi donvi,DSJ_KhoHang kho,String ID){
		Integer id = Integer.parseInt(ID);
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("UPDATE XuatHang SET HangHoa_ID=?, TenHangHoa=?,KhoHang=?,TenDonVi=?,DonGia=?,SoLuong=?,ThanhTien=? WHERE XuatHang_ID=?");
			st.setInt(8, id);
			st.setString(1, person.MaSP);
			st.setString(2, person.TenSP);
			st.setString(3, kho.TenKho);
			st.setString(4, donvi.TenDV);	
			st.setString(5, person.GiaBan);
			st.setString(6, person.SoLuong);
			st.setString(7, person.ThanhTien);
			st.executeUpdate();			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			UtilDelegate.closeConnection(con);
		}
		
	}
	public void del(String ID){
		Integer id = Integer.parseInt(ID);
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("Delete FROM XuatHang WHERE XuatHang_ID ="+id);
			//st.setString(1, ID);			
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	public String selectmaXH(DSJ_XuatHang data){
		String iD_GDBanHang = null;
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select top 1 ID_GDBanHang from (select top (select count(*) from KetThucGDBanHang) * from KetThucGDBanHang order by ID_GDBanHang DESC)a");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				iD_GDBanHang = rs.getString("ID_GDBanHang");
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			UtilDelegate.closeConnection(con);
		}
		return iD_GDBanHang;
	}
	//thu tien
	public void insertGiaoDichBanHang(DSJ_XuatHang xuathang,DSJ_KhachHang khachhang){
		Connection con =UtilDelegate.getConnection();
		try {
			Statement st = con.createStatement();
			String str ="INSERT INTO KetThucGDBanHang(ID_KhachHang,TenKH,ChungTu,Ngay,TongThanhToan,DaTra,ConLai,GhiChu,LoaiChungTu) " + "VALUES (" +
			"'"+ khachhang.getMaKH()+ "',N'"+ khachhang.getHoTenKH()+ "','"+ xuathang.getChungTu()+ "'," +
			"'"+ xuathang.getNgay()+ "', '"+ xuathang.getTongThanhToan()+ "', '"+ xuathang.getDaTra()+ "'," +
			" '"+ xuathang.getConLai()+ "', N'"+xuathang.getGhiChu()+ "', N'"+xuathang.getLoaiChungTu()+ "')";	
			st.execute(str);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			UtilDelegate.closeConnection(con);
		}
	}
	
	public Vector getThuTien(){
		Vector list = new Vector();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select * from KetThucGDBanHang");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Vector<String> b = new Vector<String>();
				b.add(rs.getString("ChungTu"));
				b.add(rs.getString("ID_KhachHang"));
				b.add(rs.getString("TenKH"));				
				b.add(rs.getString("Ngay"));
				b.add(rs.getString("TongThanhToan"));
				b.add(rs.getString("DaTra"));
				b.add(rs.getString("ConLai"));
				b.add(rs.getString("GhiChu"));
				b.add(rs.getString("LoaiChungTu"));
				list.add(b);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}
	
	public Vector getThuTienxem(ThuTien thutien){
		this.thutien =thutien;
		java.util.Date datestart =  thutien.dateStart.getDate();
		String dates = String.format("%1$td-%1$tm-%1$tY", datestart);
		java.util.Date dateend =  thutien.dateEnd.getDate();
		String dated = String.format("%1$td-%1$tm-%1$tY", dateend);
		Vector list = new Vector();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("SELECT * FROM KetThucGDBanHang WHERE Ngay BETWEEN '" +dates+ "' AND '" +dated+"'");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Vector<String> b = new Vector<String>();
				b.add(rs.getString("ChungTu"));
				b.add(rs.getString("ID_KhachHang"));
				b.add(rs.getString("TenKH"));				
				b.add(rs.getString("Ngay"));
				b.add(rs.getString("TongThanhToan"));
				b.add(rs.getString("DaTra"));
				b.add(rs.getString("ConLai"));
				b.add(rs.getString("GhiChu"));
				b.add(rs.getString("LoaiChungTu"));
				list.add(b);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}
	
	public Vector getChungTuxem1(ChungTu chungtu){
		this.chungtu =chungtu;
		java.util.Date datestart =  chungtu.dateStart.getDate();
		String dates = String.format("%1$td-%1$tm-%1$tY", datestart);
		java.util.Date dateend =  chungtu.dateEnd.getDate();
		String dated = String.format("%1$td-%1$tm-%1$tY", dateend);
		Vector list = new Vector();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("SELECT * FROM KetThucGDBanHang WHERE Ngay BETWEEN '" +dates+ "' AND '" +dated+"'");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Vector<String> b = new Vector<String>();
				b.add(rs.getString("ChungTu"));
				b.add(rs.getString("ID_KhachHang"));
				b.add(rs.getString("TenKH"));				
				b.add(rs.getString("Ngay"));
				b.add(rs.getString("TongThanhToan"));
				b.add(rs.getString("DaTra"));
				b.add(rs.getString("ConLai"));
				b.add(rs.getString("GhiChu"));
				b.add(rs.getString("LoaiChungTu"));
				list.add(b);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}
	//doanh thu
	public Vector getDoanhThu(DoanhThu doanhthu){
		this.doanhthu =doanhthu;
		java.util.Date datestart =  doanhthu.dateStart.getDate();
		String dates = String.format("%1$td-%1$tm-%1$tY", datestart);
		java.util.Date dateend =  doanhthu.dateEnd.getDate();
		String dated = String.format("%1$td-%1$tm-%1$tY", dateend);
		Vector list = new Vector();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select Ngay,Sum(TongThanhToan)as TongThanhToan  from KetThucGDBanHang where Ngay BETWEEN'"+dates+"' AND '"+dated+"' group by Ngay");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Vector<String> b = new Vector<String>();
				b.add(rs.getString("Ngay"));
				b.add(rs.getString("TongThanhToan"));
				list.add(b);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}
	
	
}
